﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: March 7 2016
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[_OLD_ViewEntityRelationships_Changed] 
	-- The entity whose permissions need to be returned
	@RequestorEntityID nvarchar(100), 
	-- The date for filtering any relationships that contain a start or end date
	@FilterDate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @EntityType nvarchar(100),@ShortRelationshipName nvarchar(12),@SuperRelationship nvarchar(12)
	DECLARE @Entity1Type nvarchar(100),@Entity2Type nvarchar(100),@BuildSQL nvarchar(MAX)
	DECLARE @E1CAE2 int,@E1CACE2 int,@E2CAE1 int,@E2CACE1 int,@FriendlyID nvarchar(MAX)
	DECLARE @ExternalEntityType nvarchar(100)

	CREATE TABLE #EntityRelationships (
		E1ID nvarchar(100), 
		E2ID nvarchar(100), 
		ShortRelationshipName nvarchar(20),
		IsRevRelationship nvarchar(20), 
		StartDate datetime, 
		EndDate datetime, 
		SuperRelationship nvarchar(20), 
		E1CAE2 int, 
		E1CACE2 int,
		Entity1Type nvarchar(100),
		Entity2Type nvarchar(100),
		RelationshipName nvarchar(50)
	)
	CREATE TABLE #EntityTypeTable (
		EntityID nvarchar(100),
		EntityType nvarchar(100)
	)

    --SELECT * FROM ViewEntityRelationships(CAST(@RequestorEntityID as int),@FilterDate,'')

	-- If @RequestorEntityID is numeric, start with the local relationships
	IF ISNUMERIC(@RequestorEntityID) = 1 BEGIN
		INSERT INTO #EntityRelationships(E1ID, E2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, E1CAE2, E1CACE2,Entity1Type,Entity2Type,RelationshipName)
		SELECT CAST(E1ID AS nvarchar), CAST(E2ID AS nvarchar), ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, E1CAE2, E1CACE2,Entity1Type,Entity2Type,RelationshipName
		FROM 
			(SELECT  ER.Entity1ID E1ID, ER.Entity2ID E2ID, ER.ShortRelationshipName,0 As IsRevRelationship, ER.StartDate, ER.EndDate, DER.SuperRelationship, DER.Entity1_Can_Access_Entity2 AS E1CAE2, 
						DER.Entity1_Can_Access_Childof_Entity2 AS E1CACE2,Entity1Type,Entity2Type,RelationshipName
			FROM      dbo.EntityRelationships AS ER INNER JOIN
							   dbo.DefinedEntityRelationships AS DER ON DER.ShortRelationshipName = ER.ShortRelationshipName
			WHERE  (ER.Entity1ID = @RequestorEntityID) AND (StartDate IS NULL OR StartDate < @FilterDate) AND (EndDate IS NULL OR EndDate > @FilterDate) 
			UNION
			SELECT        ER.Entity2ID AS E1ID, ER.Entity1ID AS E2ID, ER.ShortRelationshipName,1 As IsRevRelationship, ER.StartDate, ER.EndDate, DER.SuperRelationship, DER.Entity2_Can_Access_Entity1 AS E1CAE2,
								DER.Entity2_Can_Access_Childof_Entity1 AS E1CACE2,Entity2Type AS Entity1Type,Entity1Type AS Entity2Type,ReverseRelationship AS RelationshipName
			FROM            dbo.EntityRelationships AS ER INNER JOIN
									 dbo.DefinedEntityRelationships AS DER ON DER.ShortRelationshipName = ER.ShortRelationshipName
			WHERE        (ER.Entity2ID = @RequestorEntityID) AND (NOT (DER.ReverseRelationship IS NULL))
						 AND (StartDate IS NULL OR StartDate < @FilterDate) AND (EndDate IS NULL OR EndDate > @FilterDate)
			) AS RelTable
		-- Get the EntityType of RequestorEntityID
		SELECT @EntityType = EntityType FROM Entities WHERE @RequestorEntityID = EntityID
	END
	-- Once local relationships have been established, external relationships need to be checked
	
	-- IF RequestorEntityID does not exist in the entities table, the following loop is necessary
	IF @EntityType IS NULL BEGIN
		-- Loop through all external defined entity tables to find the table with the EntityID. This will reveal
		-- the entity type.
		DECLARE @EntityTypeCursor CURSOR
		SET @EntityTypeCursor = CURSOR FAST_FORWARD
		FOR 
			SELECT ExternalEntityType,RafikiNOSQL FROM DefinedExternalEntityTypes
		
		OPEN @EntityTypeCursor
		FETCH NEXT FROM @EntityTypeCursor
		INTO @ExternalEntityType,@BuildSQL
		WHILE @@FETCH_STATUS = 0 AND @EntityType IS NULL
		BEGIN
			DELETE FROM #EntityTypeTable
			SET @BuildSQL = dbo.ReadDataFromDB_ConvertSQL(@BuildSQL,@RequestorEntityID,'','','')
			SET @BuildSQL = 'SELECT EntityID,''' + @ExternalEntityType + ''' AS EntityType FROM (' + @BuildSQL + ') AS A'
			SET @BuildSQL = 'INSERT INTO #EntityTypeTable(EntityID,EntityType) ' + @BuildSQL
			EXEC (@BuildSQL)

			SELECT @EntityType = EntityType FROM #EntityTypeTable

			FETCH NEXT FROM @EntityTypeCursor
			INTO @ExternalEntityType,@BuildSQL
		END
		CLOSE @EntityTypeCursor
		DEALLOCATE @EntityTypeCursor

	END

	IF @EntityType IS NULL BEGIN
		SELECT * FROM #EntityRelationships
		RETURN
	END

	

	-- Query ExternalEntityRelationships for relationships that have the specified EntityType either in Entity1Type or Entity2Type
	-- Loop through executing each relationship and filtering for the correct relationship ID.  Any valid results can be inserted into
	-- the #EntityRelationships table.
	DECLARE @ExternalRelationships CURSOR
	SET @ExternalRelationships = CURSOR FAST_FORWARD
	FOR 
		SELECT ShortRelationshipName,SuperRelationship,Entity1Type,Entity2Type,Entity1_Can_Access_Entity2,Entity1_Can_Access_Childof_Entity2,
				Entity2_Can_Access_Entity1,Entity2_Can_Access_Childof_Entity1
		FROM DefinedExternalEntityRelationships WHERE (@EntityType = Entity1Type OR @EntityType = Entity2Type)

	OPEN @ExternalRelationships
	FETCH NEXT FROM @ExternalRelationships
	INTO @ShortRelationshipName,@SuperRelationship,@Entity1Type,@Entity2Type,@E1CAE2,@E1CACE2,@E2CAE1,@E2CACE1
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- There are two separate if statements rather than an if/else because the entity could be Entity1ID AND Entity2ID
		IF @Entity1Type = @EntityType BEGIN
			-- INSERT directly into #EntityRelationships
			SET @BuildSQL = 'SELECT Entity1ID AS E1ID,Entity2ID AS E2ID,"' + @ShortRelationshipName + '" AS ShortRelationshipName, ' 
						  + '0 AS IsRevRelationship,StartDate,EndDate,' + COALESCE('"' + @SuperRelationship + '"','NULL') + ' AS SuperRelationship, '
						  + COALESCE(CAST(@E1CAE2 AS NVARCHAR),'NULL') + ' AS E1CAE2,' 
						  + COALESCE(CAST(@E1CACE2 AS NVARCHAR),'NULL') + ' AS E1CACE2,Entity1Type,Entity2Type,RelationshipName 
							FROM [{' + @ShortRelationshipName + '} WHERE Entity1ID = "' + @RequestorEntityID + '" AND "'
						  + CAST(@FilterDate AS NVARCHAR) + '" BETWEEN STARTDATE AND ENDDATE ] INNER JOIN DefinedExternalEntityRelationships AS DEER '
						  + 'ON "' + @ShortRelationshipName + '" = DEER.ShortRelationshipName'
			SET @FriendlyID = NULL
			-- If it is a local entity, try to find a FriendlyID to use for the connection
			SELECT @FriendlyID = AttributeValueString FROM Attributes A INNER JOIN DefinedAttributes DA ON A.AttributeID = DA.AttributeID
				WHERE CAST(EntityID AS NVARCHAR) = @RequestorEntityID AND DA.AttributeName='FriendlyID'

			SET @BuildSQL = dbo.ReadDataFromDB_ConvertSQL(@BuildSQL,COALESCE(@FriendlyID,''),'','','')
			IF LEFT(@BuildSQL,6) <> 'Error:' BEGIN
				--PRINT @ShortRelationshipName + '**************************************************'
				--PRINT @BuildSQL
				--INSERT INTO #EntityRelationships(E1ID, E2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, E1CAE2, E1CACE2)
				EXECUTE('INSERT INTO #EntityRelationships(E1ID, E2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, 
							SuperRelationship, E1CAE2, E1CACE2,Entity1Type,Entity2Type,RelationshipName) ' + @BuildSQL)
			END
		END
		
		IF @Entity2Type = @EntityType BEGIN
			-- Reverse, then insert into #EntityRelationships
			SET @BuildSQL = 'SELECT Entity2ID AS E1ID,Entity1ID AS E2ID,"' + @ShortRelationshipName + '" AS ShortRelationshipName, ' 
						  + '1 AS IsRevRelationship,StartDate,EndDate,' + COALESCE('"' + @SuperRelationship + '"','NULL') + ' AS SuperRelationship, '
						  + COALESCE(CAST(@E2CAE1 AS NVARCHAR),'NULL') + ' AS E1CAE2,' 
						  + COALESCE(CAST(@E2CACE1 AS NVARCHAR),'NULL') + ' AS E1CACE2,Entity1Type,Entity2Type,ReverseRelationship AS RelationshipName 
							FROM [{' + @ShortRelationshipName + '} WHERE Entity2ID = "' + @RequestorEntityID + '" AND "'
						  + CAST(@FilterDate AS NVARCHAR) + '" BETWEEN STARTDATE AND ENDDATE ] INNER JOIN DefinedExternalEntityRelationships AS DEER '
						  + 'ON "' + @ShortRelationshipName + '" = DEER.ShortRelationshipName'
			SET @FriendlyID = NULL
			-- If it is a local entity, try to find a FriendlyID to use for the connection
			SELECT @FriendlyID = AttributeValueString FROM Attributes A INNER JOIN DefinedAttributes DA ON A.AttributeID = DA.AttributeID
				WHERE CAST(EntityID AS NVARCHAR) = @RequestorEntityID AND DA.AttributeName='FriendlyID'

			SET @BuildSQL = dbo.ReadDataFromDB_ConvertSQL(@BuildSQL,COALESCE(@FriendlyID,''),'','','')
			IF LEFT(@BuildSQL,6) <> 'Error:' BEGIN
				--PRINT @ShortRelationshipName + '**************************************************'
				--PRINT @BuildSQL
				--INSERT INTO #EntityRelationships(E1ID, E2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, SuperRelationship, E1CAE2, E1CACE2)
				EXECUTE('INSERT INTO #EntityRelationships(E1ID, E2ID, ShortRelationshipName,IsRevRelationship, StartDate, EndDate, 
							SuperRelationship, E1CAE2, E1CACE2,Entity1Type,Entity2Type,RelationshipName) ' + @BuildSQL)
			END
		END

		FETCH NEXT FROM @ExternalRelationships
		INTO @ShortRelationshipName,@SuperRelationship,@Entity1Type,@Entity2Type,@E1CAE2,@E1CACE2,@E2CAE1,@E2CACE1
	END
	CLOSE @ExternalRelationships
	DEALLOCATE @ExternalRelationships

	SELECT * FROM #EntityRelationships
END